/*
 Navicat Premium Data Transfer

 Source Server         : postgre
 Source Server Type    : PostgreSQL
 Source Server Version : 160002 (160002)
 Source Host           : localhost:5432
 Source Catalog        : vidspark
 Source Schema         : public

 Target Server Type    : PostgreSQL
 Target Server Version : 160002 (160002)
 File Encoding         : 65001

 Date: 01/01/2025 23:19:42
*/


-- ----------------------------
-- Sequence structure for chat_group_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."chat_group_id_seq";
CREATE SEQUENCE "public"."chat_group_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for comment_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."comment_id_seq";
CREATE SEQUENCE "public"."comment_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for comment_id_seq1
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."comment_id_seq1";
CREATE SEQUENCE "public"."comment_id_seq1" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for comment_id_seq2
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."comment_id_seq2";
CREATE SEQUENCE "public"."comment_id_seq2" 
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for emoji_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."emoji_id_seq";
CREATE SEQUENCE "public"."emoji_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for favority_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."favority_id_seq";
CREATE SEQUENCE "public"."favority_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for follow_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."follow_id_seq";
CREATE SEQUENCE "public"."follow_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for follow_id_seq1
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."follow_id_seq1";
CREATE SEQUENCE "public"."follow_id_seq1" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for follow_id_seq2
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."follow_id_seq2";
CREATE SEQUENCE "public"."follow_id_seq2" 
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for group_application_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."group_application_id_seq";
CREATE SEQUENCE "public"."group_application_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for group_member_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."group_member_id_seq";
CREATE SEQUENCE "public"."group_member_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for like_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."like_id_seq";
CREATE SEQUENCE "public"."like_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for like_id_seq1
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."like_id_seq1";
CREATE SEQUENCE "public"."like_id_seq1" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for like_id_seq2
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."like_id_seq2";
CREATE SEQUENCE "public"."like_id_seq2" 
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for share_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."share_id_seq";
CREATE SEQUENCE "public"."share_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for tag_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."tag_id_seq";
CREATE SEQUENCE "public"."tag_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for tag_id_seq1
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."tag_id_seq1";
CREATE SEQUENCE "public"."tag_id_seq1" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for tag_id_seq2
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."tag_id_seq2";
CREATE SEQUENCE "public"."tag_id_seq2" 
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for work_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."work_id_seq";
CREATE SEQUENCE "public"."work_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for work_id_seq1
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."work_id_seq1";
CREATE SEQUENCE "public"."work_id_seq1" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for work_id_seq2
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."work_id_seq2";
CREATE SEQUENCE "public"."work_id_seq2" 
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

-- ----------------------------
-- Table structure for cached_user
-- ----------------------------
DROP TABLE IF EXISTS "public"."cached_user";
CREATE TABLE "public"."cached_user" (
  "id" uuid NOT NULL,
  "username" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
  "email" varchar(254) COLLATE "pg_catalog"."default" NOT NULL,
  "avatar" text COLLATE "pg_catalog"."default" NOT NULL,
  "gender" char(1) COLLATE "pg_catalog"."default" NOT NULL
)
;
COMMENT ON COLUMN "public"."cached_user"."id" IS '用户ID';
COMMENT ON COLUMN "public"."cached_user"."username" IS '用户名';
COMMENT ON COLUMN "public"."cached_user"."email" IS '邮箱';
COMMENT ON COLUMN "public"."cached_user"."avatar" IS '头像URL';
COMMENT ON COLUMN "public"."cached_user"."gender" IS '性别，''F''表示男性，''M''表示女性，''O''表示其它即未设置';

-- ----------------------------
-- Table structure for chat_group
-- ----------------------------
DROP TABLE IF EXISTS "public"."chat_group";
CREATE TABLE "public"."chat_group" (
  "id" int8 NOT NULL DEFAULT nextval('chat_group_id_seq'::regclass),
  "name" text COLLATE "pg_catalog"."default" NOT NULL,
  "avatar" text COLLATE "pg_catalog"."default",
  "description" text COLLATE "pg_catalog"."default",
  "is_deleted" bool NOT NULL DEFAULT false,
  "created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP
)
;
COMMENT ON COLUMN "public"."chat_group"."name" IS '群名';
COMMENT ON COLUMN "public"."chat_group"."avatar" IS '群头像';
COMMENT ON COLUMN "public"."chat_group"."description" IS '群简介';
COMMENT ON COLUMN "public"."chat_group"."is_deleted" IS '逻辑删除';

-- ----------------------------
-- Table structure for comment
-- ----------------------------
DROP TABLE IF EXISTS "public"."comment";
CREATE TABLE "public"."comment" (
  "id" int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY (
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1
),
  "work_id" int8 NOT NULL,
  "user_id" uuid NOT NULL,
  "content" text COLLATE "pg_catalog"."default" NOT NULL,
  "parent_id" int8,
  "is_deleted" bool NOT NULL DEFAULT false,
  "created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "is_top" bool NOT NULL DEFAULT false
)
;
COMMENT ON COLUMN "public"."comment"."id" IS '评论ID';
COMMENT ON COLUMN "public"."comment"."work_id" IS '作品ID';
COMMENT ON COLUMN "public"."comment"."user_id" IS '发表评论的用户ID';
COMMENT ON COLUMN "public"."comment"."content" IS '评论内容';
COMMENT ON COLUMN "public"."comment"."parent_id" IS '该字段不为空时表示此条数据为回复评论，该字段为回复的评论ID';
COMMENT ON COLUMN "public"."comment"."is_deleted" IS '逻辑删除字段';
COMMENT ON COLUMN "public"."comment"."is_top" IS '是否置顶';

-- ----------------------------
-- Table structure for emoji
-- ----------------------------
DROP TABLE IF EXISTS "public"."emoji";
CREATE TABLE "public"."emoji" (
  "id" int8 NOT NULL DEFAULT nextval('emoji_id_seq'::regclass),
  "user_id" uuid NOT NULL,
  "description" text COLLATE "pg_catalog"."default",
  "image" bytea NOT NULL,
  "created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP
)
;
COMMENT ON COLUMN "public"."emoji"."user_id" IS '用户ID';
COMMENT ON COLUMN "public"."emoji"."description" IS '描述';
COMMENT ON COLUMN "public"."emoji"."image" IS '二进制文件';

-- ----------------------------
-- Table structure for favority
-- ----------------------------
DROP TABLE IF EXISTS "public"."favority";
CREATE TABLE "public"."favority" (
  "id" int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY (
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1
),
  "work_id" int8 NOT NULL,
  "user_id" uuid NOT NULL,
  "is_deleted" bool NOT NULL DEFAULT false,
  "created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP
)
;
COMMENT ON COLUMN "public"."favority"."id" IS '收藏ID';
COMMENT ON COLUMN "public"."favority"."work_id" IS '作品ID';
COMMENT ON COLUMN "public"."favority"."user_id" IS '用户ID';
COMMENT ON COLUMN "public"."favority"."is_deleted" IS '逻辑删除';

-- ----------------------------
-- Table structure for follow
-- ----------------------------
DROP TABLE IF EXISTS "public"."follow";
CREATE TABLE "public"."follow" (
  "id" int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY (
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1
),
  "follower_id" uuid NOT NULL,
  "followee_id" uuid NOT NULL,
  "created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP
)
;
COMMENT ON COLUMN "public"."follow"."id" IS '关注ID';
COMMENT ON COLUMN "public"."follow"."follower_id" IS '关注发起者ID';
COMMENT ON COLUMN "public"."follow"."followee_id" IS '被关注者ID';

-- ----------------------------
-- Table structure for group_application
-- ----------------------------
DROP TABLE IF EXISTS "public"."group_application";
CREATE TABLE "public"."group_application" (
  "id" int8 NOT NULL DEFAULT nextval('group_application_id_seq'::regclass),
  "group_id" int8 NOT NULL,
  "user_id" uuid NOT NULL,
  "status" int2 NOT NULL DEFAULT 0,
  "apply_reason" text COLLATE "pg_catalog"."default",
  "created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP
)
;
COMMENT ON COLUMN "public"."group_application"."group_id" IS '群组ID';
COMMENT ON COLUMN "public"."group_application"."user_id" IS '申请成员ID';
COMMENT ON COLUMN "public"."group_application"."status" IS '申请状态，0-待处理，1-已通过，2-已拒绝';
COMMENT ON COLUMN "public"."group_application"."apply_reason" IS '申请理由';

-- ----------------------------
-- Table structure for group_member
-- ----------------------------
DROP TABLE IF EXISTS "public"."group_member";
CREATE TABLE "public"."group_member" (
  "id" int8 NOT NULL DEFAULT nextval('group_member_id_seq'::regclass),
  "group_id" int8 NOT NULL,
  "user_id" uuid NOT NULL,
  "nickname" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
  "role" int2 NOT NULL DEFAULT 2,
  "is_deleted" bool NOT NULL DEFAULT false,
  "created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP
)
;
COMMENT ON COLUMN "public"."group_member"."group_id" IS '群组ID';
COMMENT ON COLUMN "public"."group_member"."user_id" IS '成员ID';
COMMENT ON COLUMN "public"."group_member"."nickname" IS '群内昵称';
COMMENT ON COLUMN "public"."group_member"."role" IS '群角色';
COMMENT ON COLUMN "public"."group_member"."is_deleted" IS '逻辑删除';

-- ----------------------------
-- Table structure for like
-- ----------------------------
DROP TABLE IF EXISTS "public"."like";
CREATE TABLE "public"."like" (
  "id" int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY (
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1
),
  "work_id" int8 NOT NULL,
  "user_id" uuid NOT NULL,
  "comment_id" int4,
  "is_deleted" bool NOT NULL DEFAULT false,
  "created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP
)
;
COMMENT ON COLUMN "public"."like"."id" IS '点赞ID';
COMMENT ON COLUMN "public"."like"."work_id" IS '作品ID';
COMMENT ON COLUMN "public"."like"."user_id" IS '用户ID';
COMMENT ON COLUMN "public"."like"."comment_id" IS '该字段不为空时表示对评论的点赞，该字段为点赞评论的ID';
COMMENT ON COLUMN "public"."like"."is_deleted" IS '逻辑删除字段';

-- ----------------------------
-- Table structure for share
-- ----------------------------
DROP TABLE IF EXISTS "public"."share";
CREATE TABLE "public"."share" (
  "id" int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY (
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1
),
  "work_id" int8 NOT NULL,
  "sender_id" uuid NOT NULL,
  "receiver_id" uuid NOT NULL,
  "is_deleted" bool NOT NULL DEFAULT false,
  "created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP
)
;
COMMENT ON COLUMN "public"."share"."id" IS '转发ID';
COMMENT ON COLUMN "public"."share"."work_id" IS '作品ID';
COMMENT ON COLUMN "public"."share"."sender_id" IS '发送者ID';
COMMENT ON COLUMN "public"."share"."receiver_id" IS '接收者ID';
COMMENT ON COLUMN "public"."share"."is_deleted" IS '逻辑删除';

-- ----------------------------
-- Table structure for tag
-- ----------------------------
DROP TABLE IF EXISTS "public"."tag";
CREATE TABLE "public"."tag" (
  "id" int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY (
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1
),
  "name" varchar(50) COLLATE "pg_catalog"."default" NOT NULL
)
;
COMMENT ON COLUMN "public"."tag"."id" IS '作品标签ID';
COMMENT ON COLUMN "public"."tag"."name" IS '作品标签名';

-- ----------------------------
-- Table structure for work
-- ----------------------------
DROP TABLE IF EXISTS "public"."work";
CREATE TABLE "public"."work" (
  "id" int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY (
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1
),
  "author_id" uuid NOT NULL,
  "type" int2 NOT NULL,
  "title" text COLLATE "pg_catalog"."default",
  "cover_url" text COLLATE "pg_catalog"."default",
  "play_url" text COLLATE "pg_catalog"."default",
  "plays" int4 NOT NULL DEFAULT 0,
  "is_deleted" bool NOT NULL DEFAULT false,
  "created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP
)
;
COMMENT ON COLUMN "public"."work"."id" IS '作品ID';
COMMENT ON COLUMN "public"."work"."author_id" IS '作者ID';
COMMENT ON COLUMN "public"."work"."type" IS '作品类型，0-视频，1-照片';
COMMENT ON COLUMN "public"."work"."title" IS '视频标题';
COMMENT ON COLUMN "public"."work"."cover_url" IS '封面URL';
COMMENT ON COLUMN "public"."work"."play_url" IS '播放URL';
COMMENT ON COLUMN "public"."work"."plays" IS '播放量';
COMMENT ON COLUMN "public"."work"."is_deleted" IS '逻辑删除';

-- ----------------------------
-- Table structure for work_image
-- ----------------------------
DROP TABLE IF EXISTS "public"."work_image";
CREATE TABLE "public"."work_image" (
  "work_id" int8 NOT NULL,
  "sequence" int4 NOT NULL,
  "image_path" text COLLATE "pg_catalog"."default" NOT NULL,
  "description" text COLLATE "pg_catalog"."default",
  "is_cover" bool NOT NULL DEFAULT false,
  "created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP
)
;
COMMENT ON COLUMN "public"."work_image"."work_id" IS '作品ID';
COMMENT ON COLUMN "public"."work_image"."sequence" IS '图片序号';
COMMENT ON COLUMN "public"."work_image"."image_path" IS '图片路径';
COMMENT ON COLUMN "public"."work_image"."description" IS '图片描述';
COMMENT ON COLUMN "public"."work_image"."is_cover" IS '是否为封面';
COMMENT ON COLUMN "public"."work_image"."created_at" IS '创建时间';

-- ----------------------------
-- Table structure for work_tag
-- ----------------------------
DROP TABLE IF EXISTS "public"."work_tag";
CREATE TABLE "public"."work_tag" (
  "work_id" int8 NOT NULL,
  "tag_id" int8 NOT NULL
)
;
COMMENT ON COLUMN "public"."work_tag"."work_id" IS '作品ID';
COMMENT ON COLUMN "public"."work_tag"."tag_id" IS '作品标签ID';

-- ----------------------------
-- Function structure for check_like_unique
-- ----------------------------
DROP FUNCTION IF EXISTS "public"."check_like_unique"();
CREATE OR REPLACE FUNCTION "public"."check_like_unique"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
BEGIN
    -- 如果 comment_id 为 NULL，则确保 work_id 和 user_id 唯一
    IF NEW.comment_id IS NULL THEN
        IF EXISTS (
            SELECT 1
            FROM public."like" l
            WHERE l.work_id = NEW.work_id
              AND l.user_id = NEW.user_id
              AND l.comment_id IS NULL
              AND l.id != NEW.id -- 排除正在插入的记录
        ) THEN
            RAISE EXCEPTION 'A user can only like a work once without comment_id';
        END IF;
    END IF;

    -- 如果 comment_id 不为空，则不做额外检查
    RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- ----------------------------
-- Procedure structure for insert_tags_for_work
-- ----------------------------
DROP PROCEDURE IF EXISTS "public"."insert_tags_for_work"("p_work_id" int8, "p_tags" _text);
CREATE OR REPLACE PROCEDURE "public"."insert_tags_for_work"("p_work_id" int8, "p_tags" _text)
 AS $BODY$
DECLARE
    v_tag_id BIGINT; -- 修改为 BIGINT
    v_tag_name TEXT;
BEGIN
    -- 遍历 tags
    FOREACH v_tag_name IN ARRAY p_tags LOOP
        -- 检查 tag 是否已存在
        SELECT id INTO v_tag_id FROM tag WHERE name = v_tag_name;

        -- 如果 tag 不存在，插入 tag 表
        IF v_tag_id IS NULL THEN
            INSERT INTO tag (name)
            VALUES (v_tag_name)
            RETURNING id INTO v_tag_id;
        END IF;

        -- 插入 work_tag 表
        INSERT INTO work_tag (work_id, tag_id)
        VALUES (p_work_id, v_tag_id)
        ON CONFLICT DO NOTHING; -- 如果已存在，忽略
    END LOOP;
END;
$BODY$
  LANGUAGE plpgsql;

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."chat_group_id_seq"
OWNED BY "public"."chat_group"."id";
SELECT setval('"public"."chat_group_id_seq"', 1, true);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."comment_id_seq"
OWNED BY "public"."comment"."id";
SELECT setval('"public"."comment_id_seq"', 1, false);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."comment_id_seq1"
OWNED BY "public"."comment"."id";
SELECT setval('"public"."comment_id_seq1"', 1, false);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."comment_id_seq2"
OWNED BY "public"."comment"."id";
SELECT setval('"public"."comment_id_seq2"', 14, true);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."emoji_id_seq"
OWNED BY "public"."emoji"."id";
SELECT setval('"public"."emoji_id_seq"', 1, false);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."favority_id_seq"
OWNED BY "public"."favority"."id";
SELECT setval('"public"."favority_id_seq"', 1, false);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."follow_id_seq"
OWNED BY "public"."follow"."id";
SELECT setval('"public"."follow_id_seq"', 1, false);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."follow_id_seq1"
OWNED BY "public"."follow"."id";
SELECT setval('"public"."follow_id_seq1"', 1, false);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."follow_id_seq2"
OWNED BY "public"."follow"."id";
SELECT setval('"public"."follow_id_seq2"', 14, true);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."group_application_id_seq"
OWNED BY "public"."group_application"."id";
SELECT setval('"public"."group_application_id_seq"', 1, false);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."group_member_id_seq"
OWNED BY "public"."group_member"."id";
SELECT setval('"public"."group_member_id_seq"', 3, true);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."like_id_seq"
OWNED BY "public"."like"."id";
SELECT setval('"public"."like_id_seq"', 1, false);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."like_id_seq1"
OWNED BY "public"."like"."id";
SELECT setval('"public"."like_id_seq1"', 1, false);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."like_id_seq2"
OWNED BY "public"."like"."id";
SELECT setval('"public"."like_id_seq2"', 37, true);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."share_id_seq"
OWNED BY "public"."share"."id";
SELECT setval('"public"."share_id_seq"', 1, false);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."tag_id_seq"
OWNED BY "public"."tag"."id";
SELECT setval('"public"."tag_id_seq"', 1, false);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."tag_id_seq1"
OWNED BY "public"."tag"."id";
SELECT setval('"public"."tag_id_seq1"', 1, false);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."tag_id_seq2"
OWNED BY "public"."tag"."id";
SELECT setval('"public"."tag_id_seq2"', 23, true);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."work_id_seq"
OWNED BY "public"."work"."id";
SELECT setval('"public"."work_id_seq"', 1, false);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."work_id_seq1"
OWNED BY "public"."work"."id";
SELECT setval('"public"."work_id_seq1"', 1, false);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."work_id_seq2"
OWNED BY "public"."work"."id";
SELECT setval('"public"."work_id_seq2"', 67, true);

-- ----------------------------
-- Checks structure for table cached_user
-- ----------------------------
ALTER TABLE "public"."cached_user" ADD CONSTRAINT "cached_user_gender_check" CHECK (gender = ANY (ARRAY['M'::bpchar, 'F'::bpchar, 'O'::bpchar]));

-- ----------------------------
-- Primary Key structure for table cached_user
-- ----------------------------
ALTER TABLE "public"."cached_user" ADD CONSTRAINT "cached_user_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table chat_group
-- ----------------------------
ALTER TABLE "public"."chat_group" ADD CONSTRAINT "chat_group_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Indexes structure for table comment
-- ----------------------------
CREATE INDEX "comment_created_at_idx" ON "public"."comment" USING btree (
  "created_at" "pg_catalog"."timestamp_ops" ASC NULLS LAST
);
CREATE INDEX "comment_is_deleted_idx" ON "public"."comment" USING btree (
  "is_deleted" "pg_catalog"."bool_ops" ASC NULLS LAST
);
CREATE INDEX "comment_parent_id_idx" ON "public"."comment" USING btree (
  "parent_id" "pg_catalog"."int8_ops" ASC NULLS LAST
);
CREATE INDEX "comment_user_id_idx" ON "public"."comment" USING btree (
  "user_id" "pg_catalog"."uuid_ops" ASC NULLS LAST
);
CREATE INDEX "comment_work_id_is_deleted_idx" ON "public"."comment" USING btree (
  "work_id" "pg_catalog"."int8_ops" ASC NULLS LAST,
  "is_deleted" "pg_catalog"."bool_ops" ASC NULLS LAST
);

-- ----------------------------
-- Checks structure for table comment
-- ----------------------------
ALTER TABLE "public"."comment" ADD CONSTRAINT "comment_check" CHECK (id <> parent_id);

-- ----------------------------
-- Primary Key structure for table comment
-- ----------------------------
ALTER TABLE "public"."comment" ADD CONSTRAINT "comment_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table emoji
-- ----------------------------
ALTER TABLE "public"."emoji" ADD CONSTRAINT "emoji_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table favority
-- ----------------------------
ALTER TABLE "public"."favority" ADD CONSTRAINT "favority_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Uniques structure for table follow
-- ----------------------------
ALTER TABLE "public"."follow" ADD CONSTRAINT "follow_follower_id_followed_id_key" UNIQUE ("follower_id", "followee_id");

-- ----------------------------
-- Primary Key structure for table follow
-- ----------------------------
ALTER TABLE "public"."follow" ADD CONSTRAINT "follow_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Checks structure for table group_application
-- ----------------------------
ALTER TABLE "public"."group_application" ADD CONSTRAINT "group_application_status_check" CHECK (status = ANY (ARRAY[0, 1, 2]));

-- ----------------------------
-- Primary Key structure for table group_application
-- ----------------------------
ALTER TABLE "public"."group_application" ADD CONSTRAINT "group_application_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Checks structure for table group_member
-- ----------------------------
ALTER TABLE "public"."group_member" ADD CONSTRAINT "group_member_role_check" CHECK (role = ANY (ARRAY[0, 1, 2]));

-- ----------------------------
-- Primary Key structure for table group_member
-- ----------------------------
ALTER TABLE "public"."group_member" ADD CONSTRAINT "group_member_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Triggers structure for table like
-- ----------------------------
CREATE TRIGGER "trg_check_like_unique" BEFORE INSERT OR UPDATE ON "public"."like"
FOR EACH ROW
EXECUTE PROCEDURE "public"."check_like_unique"();

-- ----------------------------
-- Uniques structure for table like
-- ----------------------------
ALTER TABLE "public"."like" ADD CONSTRAINT "like_work_id_user_id_comment_id_key" UNIQUE ("work_id", "user_id", "comment_id");

-- ----------------------------
-- Primary Key structure for table like
-- ----------------------------
ALTER TABLE "public"."like" ADD CONSTRAINT "like_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table share
-- ----------------------------
ALTER TABLE "public"."share" ADD CONSTRAINT "forward_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Uniques structure for table tag
-- ----------------------------
ALTER TABLE "public"."tag" ADD CONSTRAINT "tag_name_key" UNIQUE ("name");

-- ----------------------------
-- Primary Key structure for table tag
-- ----------------------------
ALTER TABLE "public"."tag" ADD CONSTRAINT "tag_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Checks structure for table work
-- ----------------------------
ALTER TABLE "public"."work" ADD CONSTRAINT "work_type_check" CHECK (type = ANY (ARRAY[0, 1]));

-- ----------------------------
-- Primary Key structure for table work
-- ----------------------------
ALTER TABLE "public"."work" ADD CONSTRAINT "work_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table work_image
-- ----------------------------
ALTER TABLE "public"."work_image" ADD CONSTRAINT "work_image_pkey" PRIMARY KEY ("work_id", "sequence");

-- ----------------------------
-- Primary Key structure for table work_tag
-- ----------------------------
ALTER TABLE "public"."work_tag" ADD CONSTRAINT "work_tags_pkey" PRIMARY KEY ("work_id", "tag_id");

-- ----------------------------
-- Foreign Keys structure for table comment
-- ----------------------------
ALTER TABLE "public"."comment" ADD CONSTRAINT "comment_parent_comment_id_fkey" FOREIGN KEY ("parent_id") REFERENCES "public"."comment" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "public"."comment" ADD CONSTRAINT "comment_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."cached_user" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "public"."comment" ADD CONSTRAINT "comment_work_id_fkey" FOREIGN KEY ("work_id") REFERENCES "public"."work" ("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- ----------------------------
-- Foreign Keys structure for table emoji
-- ----------------------------
ALTER TABLE "public"."emoji" ADD CONSTRAINT "emoji_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."cached_user" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- ----------------------------
-- Foreign Keys structure for table favority
-- ----------------------------
ALTER TABLE "public"."favority" ADD CONSTRAINT "favority_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."cached_user" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "public"."favority" ADD CONSTRAINT "favority_work_id_fkey" FOREIGN KEY ("work_id") REFERENCES "public"."work" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- ----------------------------
-- Foreign Keys structure for table follow
-- ----------------------------
ALTER TABLE "public"."follow" ADD CONSTRAINT "follow_followee_id_fkey" FOREIGN KEY ("followee_id") REFERENCES "public"."cached_user" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "public"."follow" ADD CONSTRAINT "follow_follower_id_fkey" FOREIGN KEY ("follower_id") REFERENCES "public"."cached_user" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- ----------------------------
-- Foreign Keys structure for table group_application
-- ----------------------------
ALTER TABLE "public"."group_application" ADD CONSTRAINT "group_application_group_id_fkey" FOREIGN KEY ("group_id") REFERENCES "public"."chat_group" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "public"."group_application" ADD CONSTRAINT "group_application_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."cached_user" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- ----------------------------
-- Foreign Keys structure for table group_member
-- ----------------------------
ALTER TABLE "public"."group_member" ADD CONSTRAINT "group_member_group_id_fkey" FOREIGN KEY ("group_id") REFERENCES "public"."chat_group" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "public"."group_member" ADD CONSTRAINT "group_member_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."cached_user" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- ----------------------------
-- Foreign Keys structure for table like
-- ----------------------------
ALTER TABLE "public"."like" ADD CONSTRAINT "like_comment_id_fkey" FOREIGN KEY ("comment_id") REFERENCES "public"."comment" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "public"."like" ADD CONSTRAINT "like_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."cached_user" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "public"."like" ADD CONSTRAINT "like_work_id_fkey" FOREIGN KEY ("work_id") REFERENCES "public"."work" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- ----------------------------
-- Foreign Keys structure for table share
-- ----------------------------
ALTER TABLE "public"."share" ADD CONSTRAINT "share_receiver_id_fkey" FOREIGN KEY ("receiver_id") REFERENCES "public"."cached_user" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "public"."share" ADD CONSTRAINT "share_sender_id_fkey" FOREIGN KEY ("sender_id") REFERENCES "public"."cached_user" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "public"."share" ADD CONSTRAINT "share_work_id_fkey" FOREIGN KEY ("work_id") REFERENCES "public"."work" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- ----------------------------
-- Foreign Keys structure for table work
-- ----------------------------
ALTER TABLE "public"."work" ADD CONSTRAINT "work_author_id_fkey" FOREIGN KEY ("author_id") REFERENCES "public"."cached_user" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- ----------------------------
-- Foreign Keys structure for table work_image
-- ----------------------------
ALTER TABLE "public"."work_image" ADD CONSTRAINT "work_image_work_id_fkey" FOREIGN KEY ("work_id") REFERENCES "public"."work" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- ----------------------------
-- Foreign Keys structure for table work_tag
-- ----------------------------
ALTER TABLE "public"."work_tag" ADD CONSTRAINT "work_tags_tag_id_fkey" FOREIGN KEY ("tag_id") REFERENCES "public"."tag" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "public"."work_tag" ADD CONSTRAINT "work_tags_work_id_fkey" FOREIGN KEY ("work_id") REFERENCES "public"."work" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
